Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# B How PL/SQL Resolves Identifier Names

This appendix explains how PL/SQL resolves references to names in potentially ambiguous SQL and procedural statements.

This appendix contains these topics:

- What Is Name Resolution?

- Examples of Qualified Names and Dot Notation

- Differences in Name Resolution Between PL/SQL and SQL

- Understanding Capture

- Avoiding Inner Capture in DML Statements

- Qualifying References to Object Attributes and Methods

## What Is Name Resolution?

During compilation, the PL/SQL compiler determines which objects are associated with each name in a PL/SQL subprogram. A name might refer to a local variable, a table, a package, a procedure, a schema, and so on. When a subprogram is recompiled, that association might change if objects have been created or deleted.

A declaration or definition in an inner scope can hide another in an outer scope. In Example B-1, the declaration of variable `client` hides the definition of datatype `Client` because PL/SQL names are not case sensitive:

Example B-1 Resolving Global and Local Variable Names

    BEGIN
       <<block1>>
       DECLARE
          TYPE Client IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25) );
          TYPE Customer IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25) );
       BEGIN
          DECLARE
             client Customer; -- hides definition of type Client in outer scope
          -- lead1  Client;   -- not allowed; Client resolves to the variable client
             lead2  block1.Client; -- OK; refers to type Client
          BEGIN
             NULL; -- no processing, just an example of name resolution
          END;
       END;
    END;
    /

You can refer to datatype `Client` by qualifying the reference with block label `block1`.

In the following set of `CREATE` `TYPE` statements, the second statement generates a warning. Creating an attribute named `manager` hides the type named `manager`, so the declaration of the second attribute does not execute correctly.

    CREATE TYPE manager AS OBJECT (dept NUMBER);
    /
    CREATE TYPE person AS OBJECT (manager NUMBER, mgr manager) -- raises a warning;
    /

## Examples of Qualified Names and Dot Notation

During name resolution, the compiler can encounter various forms of references including simple unqualified names, dot-separated chains of identifiers, indexed components of a collection, and so on. This is shown in Example B-2.

Example B-2 Using the Dot Notation to Qualify Names

    CREATE OR REPLACE PACKAGE pkg1 AS
       m NUMBER;
       TYPE t1 IS RECORD (a NUMBER);
       v1 t1;
       TYPE t2 IS TABLE OF t1 INDEX BY PLS_INTEGER;
       v2 t2;
       FUNCTION f1 (p1 NUMBER) RETURN t1;
       FUNCTION f2 (q1 NUMBER) RETURN t2;
    END pkg1;
    /

    CREATE OR REPLACE PACKAGE BODY pkg1 AS
       FUNCTION f1 (p1 NUMBER) RETURN t1 IS
          n NUMBER;
       BEGIN
    -- (1) unqualified name
          n := m;
    -- (2) dot-separated chain of identifiers (package name used as scope
    --  qualifier followed by variable name)
          n := pkg1.m;
    -- (3) dot-separated chain of identifiers (package name used as scope
    --  qualifier followed by function name also used as scope qualifier
    --  followed by parameter name)
          n := pkg1.f1.p1;
    -- (4) dot-separated chain of identifiers (variable name followed by
    --  component selector)
          n := v1.a;
    -- (5) dot-separated chain of identifiers (package name used as scope
    --  qualifier followed by variable name followed by component selector)
          n := pkg1.v1.a;
    -- (6) indexed name followed by component selector
          n := v2(10).a;
    -- (7) function call followed by component selector
          n := f1(10).a;
    -- (8) function call followed by indexing followed by component selector
          n := f2(10)(10).a;
    -- (9) function call (which is a dot-separated chain of identifiers,
    -- including schema name used as scope qualifier followed by package
    -- name used as scope qualifier followed by function name)
    -- followed by component selector of the returned result followed
    -- by indexing followed by component selector
          n := hr.pkg1.f2(10)(10).a;
    -- (10) variable name followed by component selector
          v1.a := p1;
         RETURN v1;
       END f1;

       FUNCTION f2 (q1 NUMBER) RETURN t2 IS
       v_t1 t1;
       v_t2 t2;
       BEGIN
         v_t1.a := q1;
         v_t2(1) := v_t1;
         RETURN v_t2;
       END f2;
    END pkg1;
    /

Note that an outside reference to a private variable declared in a function body is not legal. For example, an outside reference to the variable `n` declared in function `f1`, such as `hr.pkg1.f1.n` from function `f2`, raises an error. See "Private Versus Public Items in Packages".

### Additional Examples of How to Specify Names With the Dot Notation

Dot notation is used for identifying record fields, object attributes, and items inside packages or other schemas. When you combine these items, you might need to use expressions with multiple levels of dots, where it is not always clear what each dot refers to. Here are some of the combinations:

Field or Attribute of a Function Return Value

`func_name().field_name`
`func_name().attribute_name`

Schema Object Owned by Another Schema

`schema_name.table_name`
`schema_name.procedure_name()`
`schema_name.type_name.member_name()`

Packaged Object Owned by Another User

`schema_name.package_name.procedure_name()`
`schema_name.package_name.record_name.field_name`

Record Containing an Object Type

`record_name.field_name.attribute_name`
`record_name.field_name.member_name()`

## Differences in Name Resolution Between PL/SQL and SQL

The name resolution rules for PL/SQL and SQL are similar. You can avoid the few differences if you follow the capture avoidance rules. For compatibility, the SQL rules are more permissive than the PL/SQL rules. SQL rules, which are mostly context sensitive, recognize as legal more situations and DML statements than the PL/SQL rules.

- PL/SQL uses the same name-resolution rules as SQL when the PL/SQL compiler processes a SQL statement, such as a DML statement. For example, for a name such as `HR.JOBS`, SQL matches objects in the `HR` schema first, then packages, types, tables, and views in the current schema.

- PL/SQL uses a different order to resolve names in PL/SQL statements such as assignments and procedure calls. In the case of a name `HR.JOBS`, PL/SQL searches first for packages, types, tables, and views named `HR` in the current schema, then for objects in the `HR` schema.

For information on SQL naming rules, see Oracle Database SQL Reference.

## Understanding Capture

When a declaration or type definition in another scope prevents the compiler from resolving a reference correctly, that declaration or definition is said to capture the reference. Usually this is the result of migration or schema evolution. There are three kinds of capture: inner, same-scope, and outer. Inner and same-scope capture apply only in SQL scope.

### Inner Capture

An inner capture occurs when a name in an inner scope no longer refers to an entity in an outer scope:

- The name might now resolve to an entity in an inner scope.

- The program might cause an error, if some part of the identifier is captured in an inner scope and the complete reference cannot be resolved.

If the reference points to a different but valid name, you might not know why the program is acting differently.

In the following example, the reference to `col2` in the inner `SELECT` statement binds to column `col2` in table `tab1` because table `tab2` has no column named `col2`:

    CREATE TABLE tab1 (col1 NUMBER, col2 NUMBER);
    INSERT INTO tab1 VALUES (100, 10);
    CREATE TABLE tab2 (col1 NUMBER);
    INSERT INTO tab2 VALUES (100);

    CREATE OR REPLACE PROCEDURE proc AS
       CURSOR c1 IS SELECT * FROM tab1
          WHERE EXISTS (SELECT * FROM tab2 WHERE col2 = 10);
    BEGIN
       NULL;
    END;
    /

In the preceding example, if you add a column named `col2` to table `tab2`:

    ALTER TABLE tab2 ADD (col2 NUMBER);

then procedure `proc` is invalidated and recompiled automatically upon next use. However, upon recompilation, the `col2` in the inner `SELECT` statement binds to column `col2` in table `tab2` because `tab2` is in the inner scope. Thus, the reference to `col2` is captured by the addition of column `col2` to table `tab2`.

Using collections and object types can cause more inner capture situations. In the following example, the reference to `hr.tab2.a` resolves to attribute `a` of column `tab2` in table `tab1` through table alias `hr,` which is visible in the outer scope of the query:

    CREATE TYPE type1 AS OBJECT (a NUMBER);
    /
    CREATE TABLE tab1 (tab2 type1);
    INSERT INTO tab1 VALUES ( type1(10) );
    CREATE TABLE tab2 (x NUMBER);
    INSERT INTO tab2 VALUES ( 10 );

    -- in the following, alias tab1 with same name as schema name, which
    -- is not a good practice but is used here for illustration purpose
    -- note lack of alias in second SELECT
    SELECT * FROM tab1 hr
       WHERE EXISTS (SELECT * FROM hr.tab2 WHERE x = hr.tab2.a);

In the preceding example, you might add a column named `a` to table `hr.tab2,` which appears in the inner subquery. When the query is processed, an inner capture occurs because the reference to `hr.tab2.a` resolves to column `a` of table `tab2` in schema `hr`. You can avoid inner captures by following the rules given in "Avoiding Inner Capture in DML Statements". According to those rules, you should revise the query as follows:

    SELECT * FROM hr.tab1 p1
       WHERE EXISTS (SELECT * FROM hr.tab2 p2 WHERE p2.x = p1.tab2.a);

### Same-Scope Capture

In SQL scope, a same-scope capture occurs when a column is added to one of two tables used in a join, so that the same column name exists in both tables. Previously, you could refer to that column name in a join query. To avoid an error, now you must qualify the column name with the table name.

### Outer Capture

An outer capture occurs when a name in an inner scope, which once resolved to an entity in an inner scope, is resolved to an entity in an outer scope. SQL and PL/SQL are designed to prevent outer captures. You do not need to take any action to avoid this condition.

## Avoiding Inner Capture in DML Statements

You can avoid inner capture in DML statements by following these rules:

- Specify an alias for each table in the DML statement.

- Keep table aliases unique throughout the DML statement.

- Avoid table aliases that match schema names used in the query.

- Qualify each column reference with the table alias.

Qualifying a reference with `schema_name.table_name` does not prevent inner capture if the statement refers to tables with columns of a user-defined object type.

## Qualifying References to Object Attributes and Methods

Columns of a user-defined object type allow for more inner capture situations. To minimize problems, the name-resolution algorithm includes the following rules for the use of table aliases.

### References to Attributes and Methods

All references to attributes and methods must be qualified by a table alias. When referencing a table, if you reference the attributes or methods of an object stored in that table, the table name must be accompanied by an alias. As the following examples show, column-qualified references to an attribute or method are not allowed if they are prefixed with a table name:

    CREATE TYPE t1 AS OBJECT (x NUMBER);
    /
    CREATE TABLE tb1 (col1 t1);

    BEGIN
    -- following inserts are allowed without an alias
    -- because there is no column list
      INSERT INTO tb1 VALUES ( t1(10) );
      INSERT INTO tb1 VALUES ( t1(20) );
      INSERT INTO tb1 VALUES ( t1(30) );
    END;
    /
    BEGIN
      UPDATE tb1 SET col1.x = 10 WHERE col1.x = 20; -- error, not allowed
    END;
    /
    BEGIN
      UPDATE tb1 SET tb1.col1.x = 10 WHERE tb1.col1.x = 20; -- not allowed
    END;
    /
    BEGIN
      UPDATE hr.tb1 SET hr.tb1.col1.x = 10 WHERE hr.tb1.col1.x = 20; -- not allowed
    END;
    /
    BEGIN -- following allowed with table alias
      UPDATE hr.tb1 t set t.col1.x = 10 WHERE t.col1.x = 20;
    END;
    /
    DECLARE
      y NUMBER;
    BEGIN -- following allowed with table alias
      SELECT t.col1.x INTO y FROM tb1 t WHERE t.col1.x = 30;
    END;
    /
    BEGIN
      DELETE FROM tb1 WHERE tb1.col1.x = 10; -- not allowed
    END;
    /
    BEGIN -- following allowed with table alias
      DELETE FROM tb1 t WHERE t.col1.x = 10;
    END;
    /

### References to Row Expressions

Row expressions must resolve as references to table aliases. You can pass row expressions to operators `REF` and `VALUE`, and you can use row expressions in the `SET` clause of an `UPDATE` statement. Some examples follow:

    CREATE TYPE t1 AS OBJECT (x number);
    /
    CREATE TABLE ot1 OF t1;

    BEGIN
    -- following inserts are allowed without an alias
    -- because there is no column list
      INSERT INTO ot1 VALUES ( t1(10) );
      INSERT INTO ot1 VALUES ( 20 );
      INSERT INTO ot1 VALUES ( 30 );
    END;
    /
    BEGIN
      UPDATE ot1 SET VALUE(ot1.x) = t1(20) WHERE VALUE(ot1.x) = t1(10); -- not allowed
    END;
    /
    BEGIN -- following allowed with table alias
      UPDATE ot1 o SET o = (t1(20)) WHERE o.x = 10;
    END;
    /
    DECLARE
      n_ref REF t1;
    BEGIN -- following allowed with table alias
      SELECT REF(o) INTO n_ref FROM ot1 o WHERE VALUE(o) = t1(30);
    END;
    /
    DECLARE
      n t1;
    BEGIN -- following allowed with table alias
      SELECT VALUE(o) INTO n FROM ot1 o WHERE VALUE(o) = t1(30);
    END;
    /
    DECLARE
      n NUMBER;
    BEGIN -- following allowed with table alias
      SELECT o.x INTO n FROM ot1 o WHERE o.x = 30;
    END;
    /
    BEGIN
      DELETE FROM ot1 WHERE VALUE(ot1) = (t1(10)); -- not allowed
    END;
    /
    BEGIN -- folowing allowed with table alias
      DELETE FROM ot1 o WHERE VALUE(o) = (t1(20));
    END;
    /